package udl;

import model.Attraction;
import tool.Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OpreationAttraction {
    Util util = new Util();
    Connection connection=null;
    PreparedStatement preparedStatement;
    //添加景点
    public void addAttraction(Attraction att) throws SQLException {
        connection=util.getConnection();
        String sql = "insert into attraction(name,x,y) values (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, att.getName());
        preparedStatement.setDouble(2, att.getX());
        preparedStatement.setDouble(3, att.getY());
        preparedStatement.executeUpdate();
        preparedStatement.close();
        connection.close();
    }
    //查询点的y坐标
    public int selectY(String text1) {
        connection=util.getConnection();
        String sql="select y from attraction where name=?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, text1);
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            return resultSet.getInt("y");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //查询点的x坐标
    public int selectX(String text1) {
        connection=util.getConnection();
        String sql = "select x from attraction where name=?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, text1);
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            return resultSet.getInt("x");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //判断景点是否相同
    public boolean ifExist(String text) {
        boolean judge;
        connection=util.getConnection();
        String sql = "select * from attraction where name like '%"+text+"%'";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                judge = resultSet.next();
            }
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return judge;
    }
    //返回记录景点个数
    public int returnnum() {
        int num=0;
        connection=util.getConnection();
        String sql="select * from attraction";
        try{

            PreparedStatement ptmt=connection.prepareStatement(sql);
            ResultSet res=ptmt.executeQuery();
            while(res.next())
            {
                num++;
            }
        }catch(Exception  e)
        {
            e.printStackTrace();
        }
        return num;

    }
    //查找所有景点信息
    public List<Attraction> selectViews() {
        connection=util.getConnection();
        String sql="select * from attraction";
        List<Attraction> list=null;
        Attraction  attraction;
        try{
            list=new ArrayList<>();

            PreparedStatement ptmt=connection.prepareStatement(sql);
            ResultSet  res=ptmt.executeQuery();
            while(res.next())
            {
                attraction=new Attraction();
                attraction.setId(res.getInt("id"));
                attraction.setName(res.getString("name"));
                attraction.setX(res.getInt("x"));
                attraction.setY(res.getInt("y"));
                list.add(attraction);
            }
        }catch(Exception  e)
        {
            e.printStackTrace();
        }

        return list;
    }
    //检查该地点是否有建筑物
    public boolean check(Attraction att) {
        connection = util.getConnection();
        String sql="select * from attraction where (x between ?-50 and ?+50) and (y between ?-50 and ?+50)";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, att.getX());
            preparedStatement.setInt(2, att.getX());
            preparedStatement.setInt(3, att.getY());
            preparedStatement.setInt(4, att.getY());
            ResultSet res=preparedStatement.executeQuery();
            if(res.next()){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }
    //修改景点
    public void modifyAttraction(Attraction att) {
        connection = util.getConnection();
        String sql = "update attraction set x=? , y=? where name=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, att.getX());
            preparedStatement.setInt(2, att.getY());
            preparedStatement.setString(3, att.getName());
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //修改起始点坐标
    public void modifyRoad1(Attraction att) {
        connection = util.getConnection();
        String sql = "update  road set start_x=? ,start_y=? where start=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, att.getX());
            preparedStatement.setInt(2, att.getY());
            preparedStatement.setString(3, att.getName());
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //修改终点坐标
    public void modifyRoad2(Attraction att) {
        connection = util.getConnection();
        String sql = "update  road set end_x=?,end_y=? where end=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, att.getX());
            preparedStatement.setInt(2, att.getY());
            preparedStatement.setString(3, att.getName());
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
